package com.situ.userWeb.Dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.userWeb.Dao.IUserDao;
import com.situ.userWeb.Util.JDBCUtil;
import com.situ.userWeb.entity.Department;
import com.situ.userWeb.entity.Role;
import com.situ.userWeb.entity.User;
import com.situ.userWeb.entity.UserRoleDepartment;



public class UserDaoImpl implements IUserDao{
	@Override
	public List<UserRoleDepartment> selectAll() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		ArrayList<UserRoleDepartment> list = new ArrayList<UserRoleDepartment>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT u.id 'userId',u.name,u.age,u.password,u.email,d.name 'DEName',GROUP_CONCAT(r.name) 'RoleName',GROUP_CONCAT(r.sn) 'SN' FROM `user` AS u "
					+ "INNER JOIN department AS d ON u.department_id=d.id "
					+ "LEFT JOIN user_role AS ur ON u.id=ur.user_id "
					+ "LEFT JOIN role AS r ON ur.role_id=r.id GROUP BY u.id";
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int userId = resultSet.getInt("userId");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String password = resultSet.getString("password");
				String email = resultSet.getString("email");
				String depName = resultSet.getString("DEName");
				String RoleName = resultSet.getString("RoleName");
				String sn = resultSet.getString("SN");
				UserRoleDepartment user = new UserRoleDepartment(userId, name, password, email, age, RoleName, sn, depName);
				list.add(user);
			}
			for (UserRoleDepartment user : list) {
				System.out.println(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		return list;
	}
	@Override
	public int delete(int id) {
		Connection connection = null;
		PreparedStatement statement = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "DELETE FROM `user` WHERE id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			count = statement.executeUpdate();
			System.out.println(statement);
			System.out.println("count:" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
		return count;
	}
	@Override
	public int deleteUR(int id) {
		Connection connection = null;
		PreparedStatement statement = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "DELETE FROM `user_role` WHERE user_id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			count = statement.executeUpdate();
			System.out.println(statement);
			System.out.println("count:" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
		return count;
	}
	@Override
	public int insertUR(int userId, int roleId) {
		int count = 0;
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO user_role(`user_id`,`role_id`) VALUES(?,?)";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, userId);
			statement.setInt(2, roleId);
			count = statement.executeUpdate();
			System.out.println(statement);
			System.out.println("count:" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
		return count;
	}
	@Override
	public int insertUser(User user) {
		int count = 0;
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO user(`id`,`name`,`age`,`password`,`department_id`,`email`) VALUES(?,?,?,?,?,?)";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, user.getId());
			statement.setString(2, user.getName());
			statement.setInt(3, user.getAge());
			statement.setString(4, user.getPassword());
			statement.setInt(5, user.getDepartmentId());
			statement.setString(6, user.getEmail());
			count = statement.executeUpdate();
			System.out.println(statement);
			System.out.println("count:" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
		return count;
	}
	@Override
	public ArrayList<Role> selectRole() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		ArrayList<Role> list = new ArrayList<Role>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name` FROM role";
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int roleId = resultSet.getInt("id");
				String roleName = resultSet.getString("name");
				Role role = new Role(roleId, roleName);
				list.add(role);
			}
			for (Role role : list) {
				System.out.println(role);	
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		return list;
	}
	@Override
	public ArrayList<Department> selectDepartment() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		ArrayList<Department> list = new ArrayList<Department>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name` FROM department";
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int departmentId = resultSet.getInt("id");
				String departmentName = resultSet.getString("name");
				Department department = new Department(departmentId, departmentName);
				list.add(department);
			}
			for (Department department : list) {
				System.out.println(department);	
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		return list;
	}
	@Override
	public List<UserRoleDepartment> selectByPage(int offset, int limit) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<UserRoleDepartment> list = new ArrayList<UserRoleDepartment>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT u.id 'userId',u.name,u.age,u.password,u.email,d.name 'DEName',GROUP_CONCAT(r.name) 'RoleName',GROUP_CONCAT(r.sn) 'SN' FROM `user` AS u "
					+ "INNER JOIN department AS d ON u.department_id=d.id "
					+ "LEFT JOIN user_role AS ur ON u.id=ur.user_id "
					+ "LEFT JOIN role AS r ON ur.role_id=r.id GROUP BY u.id limit ?,?" ;
			statement = connection.prepareStatement(sql);
			statement.setInt(1, offset);
			statement.setInt(2, limit);
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int userId = resultSet.getInt("userId");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String password = resultSet.getString("password");
				String email = resultSet.getString("email");
				String depName = resultSet.getString("DEName");
				String RoleName = resultSet.getString("RoleName");
				String sn = resultSet.getString("SN");
				UserRoleDepartment user = new UserRoleDepartment(userId, name, password, email, age, RoleName, sn, depName);
				list.add(user);
			}
			for (UserRoleDepartment user : list) {
				System.out.println(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		
		return list;
	}
	@Override
	public Integer selectTotal() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT count(*) as count from `user`";
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			System.out.println(statement);
			if (resultSet.next()) {
				// count = resultSet.getInt("count");
				count = resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		
		return count;
	}
	@Override
	public User selectId(int userId) {
		User user = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id,`name`,password,email,age,department_id FROM `user` WHERE id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, userId);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String password = resultSet.getString("password");
				String email = resultSet.getString("email");
				int age = resultSet.getInt("age");
				int departmentId = resultSet.getInt("department_id");
				user = new User(id, name, password, email, age, departmentId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		
		return user;
	}
	@Override
	public int update(User user) {
		int count = 0;
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "UPDATE user SET `name`=?,password=?,email=?,`age`=?,department_id=? WHERE id=?";
			statement = connection.prepareStatement(sql);
			statement.setString(1, user.getName());
			statement.setString(2, user.getPassword());
			statement.setString(3, user.getEmail());
			statement.setInt(4, user.getAge());
			statement.setInt(5, user.getDepartmentId());
			statement.setInt(6, user.getId());
			count = statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
		return count;
	}
	@Override
	public List<Integer> selectRoleIdByUser(int id) {
		List<Integer> selectRoleId = new ArrayList<Integer>();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet =null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT role_id FROM `user_role` WHERE user_id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int roleId = resultSet.getInt("role_id");
				selectRoleId.add(roleId);
			}
			System.out.println(selectRoleId);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		return selectRoleId;
	}



}
